#How did the prescription of the top 10 most prescribed antibiotics in 2019 change due to lockdown
#How did covid-19 pandemic and lockdown affect the prescription trends of the most common antibiotics in Scotland. (Would it be better to focus on the main lockdown (May 2020) or also compare between lockdowns?)
First I will load the appropriate libraries. Then, load, clean and filter the data.
First, I loaded the datasets and created a function to concisely use the same functions on different datasets. I learned how to do this from the website https://www.dataquest.io/blog/write-functions-in-r/. In this function I filtered for antibiotics by only looking at prescriptions with the BNFItemCode starting with 0501. Then I filtered out NAs, cleaned up the names and calculated the number of prescriptions. I then merged the datasets
may2020 <- read_csv(here("data","pitc202005.csv"))
may2024 <- read_csv(here("data","pitc202405.csv"))
may2019 <- read_csv(here("data","pitc201905.csv"))
may2019 <- may2019 %>%
rename("HBT" = HBT2014)
process_antibiotics <- function(data) {
data %>%
filter(str_detect(BNFItemCode, "^0501"), !is.na(BNFItemDescription)) %>%
mutate(BNFItemDescription = str_replace(BNFItemDescription, "_", " "),
Antibiotic = str_extract(BNFItemDescription, "^[^ ]+")) #Extracts the first word, which is the antibiotic name
}
#I filtered for prescriptions with an item code beginning with 0501 because prescriptions with that item code are antibiotics (Open Prescribing, 2019).
may2019_processed <- process_antibiotics(may2019)
may2020_processed <- process_antibiotics(may2020)
may2024_processed <- process_antibiotics(may2024)
#I split this into 2 functions as later I want to group by HBT
antibiotics_by_name <- function(data2) {
data2 %>%
group_by(Antibiotic) %>%
summarise(quantity_sum = sum(PaidQuantity), .groups = "drop") %>%
arrange(desc(quantity_sum))
}
#I filtered for prescriptions with an item code beginning with 0501 because prescriptions with that item code are antibiotics (Open Prescribing, 2019).
may2019_processed <- antibiotics_by_name(may2019_processed)
may2020_processed <- antibiotics_by_name(may2020_processed)
may2024_processed <- antibiotics_by_name(may2024_processed)
I then prepared the data for joining by renaming Fluclox to Flucloxacillin. I then joined the data, and tidied it
may2020_processed <- may2020_processed %>%
mutate(Antibiotic = ifelse(Antibiotic == "FLUCLOX", "FLUCLOXACILLIN", Antibiotic))
may2019_processed <- may2019_processed %>%
mutate(Antibiotic = ifelse(Antibiotic == "FLUCLOX", "FLUCLOXACILLIN", Antibiotic))
antibiotics1920 <- full_join(may2019_processed, may2020_processed, by = "Antibiotic")
antibiotics192024 <- full_join(antibiotics1920, may2024_processed, by = "Antibiotic")
antibiotics192024 <- antibiotics192024 %>%
rename(
"May 2019" = quantity_sum.x,
"May 2020" = quantity_sum.y,
"May 2024" = quantity_sum) %>%
slice(1:10)
antibiotics192024 <- antibiotics192024 %>%
mutate(Antibiotic = str_to_sentence(Antibiotic)) #I changed all the antibiotic names to only have the first letter capitalised. I learnt this from the R help page
I created a stacked bar graph to visually compare prescriptions
pivoted_antibiotics <- antibiotics192024 %>%
pivot_longer(
cols = c("May 2019", "May 2020", "May 2024"),
names_to = "Year",
values_to = "Prescription_Number")
options(scipen=999)
antibiotics_graph <- pivoted_antibiotics %>%
ggplot(aes(x = Prescription_Number, y = Year, fill = Antibiotic)) +
geom_bar(stat = "identity") +
labs(title = "Prescriptions Sold for Each Antibiotic by Year") +
scale_fill_brewer(palette = "Paired")
theme_minimal()
## List of 136
## $ line :List of 6
## ..$ colour : chr "black"
## ..$ linewidth : num 0.5
## ..$ linetype : num 1
## ..$ lineend : chr "butt"
## ..$ arrow : logi FALSE
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_line" "element"
## $ rect :List of 5
## ..$ fill : chr "white"
## ..$ colour : chr "black"
## ..$ linewidth : num 0.5
## ..$ linetype : num 1
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_rect" "element"
## $ text :List of 11
## ..$ family : chr ""
## ..$ face : chr "plain"
## ..$ colour : chr "black"
## ..$ size : num 11
## ..$ hjust : num 0.5
## ..$ vjust : num 0.5
## ..$ angle : num 0
## ..$ lineheight : num 0.9
## ..$ margin : 'margin' num [1:4] 0points 0points 0points 0points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : logi FALSE
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ title : NULL
## $ aspect.ratio : NULL
## $ axis.title : NULL
## $ axis.title.x :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : NULL
## ..$ vjust : num 1
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : 'margin' num [1:4] 2.75points 0points 0points 0points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.title.x.top :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : NULL
## ..$ vjust : num 0
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : 'margin' num [1:4] 0points 0points 2.75points 0points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.title.x.bottom : NULL
## $ axis.title.y :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : NULL
## ..$ vjust : num 1
## ..$ angle : num 90
## ..$ lineheight : NULL
## ..$ margin : 'margin' num [1:4] 0points 2.75points 0points 0points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.title.y.left : NULL
## $ axis.title.y.right :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : NULL
## ..$ vjust : num 1
## ..$ angle : num -90
## ..$ lineheight : NULL
## ..$ margin : 'margin' num [1:4] 0points 0points 0points 2.75points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.text :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : chr "grey30"
## ..$ size : 'rel' num 0.8
## ..$ hjust : NULL
## ..$ vjust : NULL
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : NULL
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.text.x :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : NULL
## ..$ vjust : num 1
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : 'margin' num [1:4] 2.2points 0points 0points 0points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.text.x.top :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : NULL
## ..$ vjust : num 0
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : 'margin' num [1:4] 0points 0points 2.2points 0points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.text.x.bottom : NULL
## $ axis.text.y :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : num 1
## ..$ vjust : NULL
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : 'margin' num [1:4] 0points 2.2points 0points 0points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.text.y.left : NULL
## $ axis.text.y.right :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : num 0
## ..$ vjust : NULL
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : 'margin' num [1:4] 0points 0points 0points 2.2points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.text.theta : NULL
## $ axis.text.r :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : num 0.5
## ..$ vjust : NULL
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : 'margin' num [1:4] 0points 2.2points 0points 2.2points
## .. ..- attr(*, "unit")= int 8
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ axis.ticks : list()
## ..- attr(*, "class")= chr [1:2] "element_blank" "element"
## $ axis.ticks.x : NULL
## $ axis.ticks.x.top : NULL
## $ axis.ticks.x.bottom : NULL
## $ axis.ticks.y : NULL
## $ axis.ticks.y.left : NULL
## $ axis.ticks.y.right : NULL
## $ axis.ticks.theta : NULL
## $ axis.ticks.r : NULL
## $ axis.minor.ticks.x.top : NULL
## $ axis.minor.ticks.x.bottom : NULL
## $ axis.minor.ticks.y.left : NULL
## $ axis.minor.ticks.y.right : NULL
## $ axis.minor.ticks.theta : NULL
## $ axis.minor.ticks.r : NULL
## $ axis.ticks.length : 'simpleUnit' num 2.75points
## ..- attr(*, "unit")= int 8
## $ axis.ticks.length.x : NULL
## $ axis.ticks.length.x.top : NULL
## $ axis.ticks.length.x.bottom : NULL
## $ axis.ticks.length.y : NULL
## $ axis.ticks.length.y.left : NULL
## $ axis.ticks.length.y.right : NULL
## $ axis.ticks.length.theta : NULL
## $ axis.ticks.length.r : NULL
## $ axis.minor.ticks.length : 'rel' num 0.75
## $ axis.minor.ticks.length.x : NULL
## $ axis.minor.ticks.length.x.top : NULL
## $ axis.minor.ticks.length.x.bottom: NULL
## $ axis.minor.ticks.length.y : NULL
## $ axis.minor.ticks.length.y.left : NULL
## $ axis.minor.ticks.length.y.right : NULL
## $ axis.minor.ticks.length.theta : NULL
## $ axis.minor.ticks.length.r : NULL
## $ axis.line : list()
## ..- attr(*, "class")= chr [1:2] "element_blank" "element"
## $ axis.line.x : NULL
## $ axis.line.x.top : NULL
## $ axis.line.x.bottom : NULL
## $ axis.line.y : NULL
## $ axis.line.y.left : NULL
## $ axis.line.y.right : NULL
## $ axis.line.theta : NULL
## $ axis.line.r : NULL
## $ legend.background : list()
## ..- attr(*, "class")= chr [1:2] "element_blank" "element"
## $ legend.margin : 'margin' num [1:4] 5.5points 5.5points 5.5points 5.5points
## ..- attr(*, "unit")= int 8
## $ legend.spacing : 'simpleUnit' num 11points
## ..- attr(*, "unit")= int 8
## $ legend.spacing.x : NULL
## $ legend.spacing.y : NULL
## $ legend.key : list()
## ..- attr(*, "class")= chr [1:2] "element_blank" "element"
## $ legend.key.size : 'simpleUnit' num 1.2lines
## ..- attr(*, "unit")= int 3
## $ legend.key.height : NULL
## $ legend.key.width : NULL
## $ legend.key.spacing : 'simpleUnit' num 5.5points
## ..- attr(*, "unit")= int 8
## $ legend.key.spacing.x : NULL
## $ legend.key.spacing.y : NULL
## $ legend.frame : NULL
## $ legend.ticks : NULL
## $ legend.ticks.length : 'rel' num 0.2
## $ legend.axis.line : NULL
## $ legend.text :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : 'rel' num 0.8
## ..$ hjust : NULL
## ..$ vjust : NULL
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : NULL
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ legend.text.position : NULL
## $ legend.title :List of 11
## ..$ family : NULL
## ..$ face : NULL
## ..$ colour : NULL
## ..$ size : NULL
## ..$ hjust : num 0
## ..$ vjust : NULL
## ..$ angle : NULL
## ..$ lineheight : NULL
## ..$ margin : NULL
## ..$ debug : NULL
## ..$ inherit.blank: logi TRUE
## ..- attr(*, "class")= chr [1:2] "element_text" "element"
## $ legend.title.position : NULL
## $ legend.position : chr "right"
## $ legend.position.inside : NULL
## $ legend.direction : NULL
## $ legend.byrow : NULL
## $ legend.justification : chr "center"
## $ legend.justification.top : NULL
## $ legend.justification.bottom : NULL
## $ legend.justification.left : NULL
## $ legend.justification.right : NULL
## $ legend.justification.inside : NULL
## $ legend.location : NULL
## $ legend.box : NULL
## $ legend.box.just : NULL
## $ legend.box.margin : 'margin' num [1:4] 0cm 0cm 0cm 0cm
## ..- attr(*, "unit")= int 1
## $ legend.box.background : list()
## ..- attr(*, "class")= chr [1:2] "element_blank" "element"
## $ legend.box.spacing : 'simpleUnit' num 11points
## ..- attr(*, "unit")= int 8
## [list output truncated]
## - attr(*, "class")= chr [1:2] "theme" "gg"
## - attr(*, "complete")= logi TRUE
## - attr(*, "validate")= logi TRUE
antibiotics_graph <- antibiotics_graph %>%
ggplotly(tooltip = c("Prescription_Number", "Antibiotic"))
antibiotics_graph
plotly - interactive graphs in R
This data visulaisation shows that the overall number of prescirptions sold decreased during lockdown and was slightly lower before lockdown than after. It also shows that the top 10 most prescribed antibiotics were not the same in every year.
I then created a gt() table to show the number of prescriptions and calculate the total prescriptions per year
all_antibiotics_table <- antibiotics192024 %>%
summarise(across(starts_with("May"), sum, na.rm = TRUE)) %>%
mutate(Antibiotic = "Total") %>%
bind_rows(antibiotics192024, .)
all_antibiotics_table <- all_antibiotics_table %>%
gt() %>%
cols_label(Antibiotic = "Antibiotic") %>%
cols_align(align = "center",
columns = everything()) %>%
tab_style(
style = cell_text(weight = "bold"),
locations = cells_column_labels(columns = everything())
) %>%
tab_header(
title = "Affect of the Covid19 pandemic on the prescription of common antibiotics",
subtitle = "Looking at the change of prescriptions for the top 10 most common antibiotics from before, during and after the longest Covid19 lockdown") %>%
tab_spanner(
label = "Number of Prescriptions",
columns = c("May 2019", "May 2020", "May 2024"))
all_antibiotics_table
| Affect of the Covid19 pandemic on the prescription of common antibiotics | |||
| Looking at the change of prescriptions for the top 10 most common antibiotics from before, during and after the longest Covid19 lockdown | |||
| Antibiotic |
Number of Prescriptions
|
||
|---|---|---|---|
| May 2019 | May 2020 | May 2024 | |
| Amoxicillin | 2875868 | 1778491.0 | 3751932.0 |
| Phenoxymethylpenicillin | 1892108 | 968754.0 | 2616743.0 |
| Flucloxacillin | 1612959 | 1475567.0 | 1733244.0 |
| Trimethoprim | 782422 | 745904.0 | 696670.0 |
| Erythromycin | 661545 | 443204.0 | 582068.0 |
| Lymecycline | 623567 | 493844.0 | 538300.0 |
| Oxytetracycline | 572089 | 428371.0 | 277441.0 |
| Doxycycline | 447389 | 387163.0 | 616170.0 |
| Co-amoxiclav | 382770 | 349958.0 | 466694.0 |
| Clarithromycin | 377144 | 230282.1 | 493464.5 |
| Total | 10227861 | 7301538.1 | 11772726.5 |
I then created a faceted geospatial map to show which areas of Scotland were prescribed more antibiotics than other areas, and how this changed over covid